# -*- coding:utf-8 -*-

indust_stock = "c:\\Users\\T\\Desktop\\stock_ana\\data_file\\stock_industry.csv"
concept_stock = "c:\\Users\\T\\Desktop\\stock_ana\\data_file\\stock_concept.csv"
area_stock = "c:\\Users\\T\\Desktop\\stock_ana\\data_file\\stock_field.csv"


import pandas as pd
import pymysql


# indust_stock = "d:\\stock_ana\\data_file\\stock_industry.csv"
# concept_stock = "d:\\stock_ana\\data_file\\stock_concept.csv"
# area_stock = "d:\\stock_ana\\data_file\\stock_field.csv"


indust_tf = pd.read_csv(indust_stock)
concept_tf = pd.read_csv(concept_stock)
area_tf = pd.read_csv(area_stock)



def create_table(table_name):
    db = pymysql.connect('localhost','root','minicooper','stock_info')
    cursor = db.cursor()
    create_table_sql = "CREATE TABLE IF NOT EXISTS {0} (CODE CHAR(6) NOT NULL PRIMARY KEY,NAME TEXT,AREA_NAME TEXT,CONCEPT_NAME TEXT,INDUST_NAME TEXT)".format(table_name)
    cursor.execute(create_table_sql)
    db.close()

def insert_area_table(table_name,data):
    db = pymysql.connect('localhost','root','minicooper','stock_info',charset="utf8")
    cursor = db.cursor()

    code = data[0]
    name = data[1]
    area_name = data[2]
    #查找语句
    parse_table_sql = "SELECT CODE,AREA_NAME FROM {0} WHERE CODE = '{1}'".format(table_name,code)
    #新建语句
    insert_table_sql = "INSERT INTO {0} (CODE,NAME,AREA_NAME) VALUES ('{1}','{2}','{3}')".format(table_name,code,name,area_name)
    #更新语句
    update_table_sql = "UPDATE {0} SET AREA_NAME = '{1}' WHERE CODE = '{2}'".format(table_name,area_name,code)

    #判断执行过程
    cursor.execute(parse_table_sql)
    parse_result = cursor.fetchone()
    if parse_result == None:
        cursor.execute(insert_table_sql)
        db.commit()
    elif parse_result[1] == None:
        cursor.execute(update_table_sql)
        db.commit()
    elif parse_result[1] == area_name:
        pass
    else:
        if area_name == None:
            pass
        elif area_name == '':
            pass
        else:
            area_name_set = parse_result[1].split(',')
            if area_name in area_name_set:
                pass
            else:
                print(parse_result[1],area_name,sep='\n')
                new_area_name = str(parse_result[1]) + ',' + str(area_name)
                new_update_table_sql = "UPDATE {0} SET AREA_NAME = '{1}' WHERE CODE = '{2}'".format(table_name,new_area_name,code)
                cursor.execute(new_update_table_sql)
                db.commit()

    db.close()


def insert_concept_table(table_name, data):
    db = pymysql.connect('localhost', 'root', 'minicooper', 'stock_info',charset="utf8")
    cursor = db.cursor()

    code = data[0]
    name = data[1]
    concept_name = data[2]
    # 查找语句
    parse_table_sql = "SELECT CODE,CONCEPT_NAME FROM {0} WHERE CODE = '{1}'".format(table_name, code)
    # 新建语句
    insert_table_sql = "INSERT INTO {0} (CODE,NAME,CONCEPT_NAME) VALUES ('{1}','{2}','{3}')".format(table_name, code, name,
                                                                                           concept_name)
    # 更新语句
    update_table_sql = "UPDATE {0} SET CONCEPT_NAME = '{1}' WHERE CODE = '{2}'".format(table_name, concept_name, code)

    cursor.execute(parse_table_sql)
    parse_result = cursor.fetchone()
    if parse_result == None:
        cursor.execute(insert_table_sql)
        db.commit()
    elif parse_result[1] == None:
        cursor.execute(update_table_sql)
        db.commit()
    elif parse_result[1] == concept_name:
        pass
    else:
        if concept_name == None:
            pass
        elif concept_name == '':
            pass
        else:
            concept_name_set = parse_result[1].split(',')
            if concept_name in concept_name_set:
                pass
            else:
                new_concept_name = parse_result[1] + ',' + concept_name
                new_update_table_sql = "UPDATE {0} SET CONCEPT_NAME = '{1}' WHERE CODE = '{2}'".format(table_name, new_concept_name,
                                                                                                    code)
                cursor.execute(new_update_table_sql)
                db.commit()

    db.close()


def insert_indust_table(table_name, data):
    db = pymysql.connect('localhost', 'root', 'minicooper', 'stock_info',charset="utf8")
    cursor = db.cursor()

    code = data[0]
    name = data[1]
    indust_name = data[2]
    # 查找语句
    parse_table_sql = "SELECT CODE,INDUST_NAME FROM {0} WHERE CODE = '{1}'".format(table_name, code)
    # 新建语句
    insert_table_sql = "INSERT INTO {0} (CODE,NAME,INDUST_NAME) VALUES ('{1}','{2}','{3}')".format(table_name, code, name,
                                                                                           indust_name)
    # 更新语句
    update_table_sql = "UPDATE {0} SET INDUST_NAME = '{1}' WHERE CODE = '{2}'".format(table_name, indust_name, code)

    # 判断执行过程
    cursor.execute(parse_table_sql)
    parse_result = cursor.fetchone()
    if parse_result == None:
        cursor.execute(insert_table_sql)
        db.commit()
    elif parse_result[1] == None:
        cursor.execute(update_table_sql)
        db.commit()
    elif parse_result[1] == indust_name:
        pass
    else:
        if indust_name == None:
            pass
        elif indust_name == '':
            pass
        else:
            indust_name_set = parse_result[1].split(',')
            if indust_name in indust_name_set:
                pass
            else:
                new_indust_name = parse_result[1] + ',' + indust_name
                new_update_table_sql = "UPDATE {0} SET INDUST_NAME = '{1}' WHERE CODE = '{2}'".format(table_name, new_indust_name,
                                                                                                    code)
                cursor.execute(new_update_table_sql)
                db.commit()

    db.close()



table_name = 'stock_info'
create_table(table_name)


for i in range(len(indust_tf)):
    indust_pickle = indust_tf[indust_tf.index == i]
    code = str(indust_pickle['code'].values[0])
    if len(code) == 3:
        new_code = '000' + code
        indust_data = [new_code, indust_pickle['name'].values[0],indust_pickle['c_name'].values[0]]
    elif len(code) == 4:
        new_code = '00' + code
        indust_data = [new_code, indust_pickle['name'].values[0],indust_pickle['c_name'].values[0]]
    elif len(code) == 5:
        new_code = '0' + code
        indust_data = [new_code, indust_pickle['name'].values[0],indust_pickle['c_name'].values[0]]
    elif len(code) == 1:
        new_code = '00000' + code
        indust_data = [new_code, indust_pickle['name'].values[0],indust_pickle['c_name'].values[0]]
    elif len(code) == 2:
        new_code = '0000' + code
        indust_data = [new_code, indust_pickle['name'].values[0],indust_pickle['c_name'].values[0]]
    else:
        indust_data = [code, indust_pickle['name'].values[0], indust_pickle['c_name'].values[0]]
    insert_indust_table(table_name,indust_data)
print("indust is OK!")

for i in range(len(concept_tf)):
    concept_pickle = concept_tf[concept_tf.index == i]
    code = str(concept_pickle['code'].values[0])
    if len(code) == 3:
        new_code = '000' + code
        concept_data = [new_code, concept_pickle['name'].values[0], concept_pickle['c_name'].values[0]]
    elif len(code) == 2:
        new_code = '0000' + code
        concept_data = [new_code, concept_pickle['name'].values[0], concept_pickle['c_name'].values[0]]
    elif len(code) == 1:
        new_code = '00000' + code
        concept_data = [new_code, concept_pickle['name'].values[0], concept_pickle['c_name'].values[0]]
    elif len(code) == 5:
        new_code = '0' + code
        concept_data = [new_code, concept_pickle['name'].values[0], concept_pickle['c_name'].values[0]]
    elif len(code) == 4:
        new_code = '00' + code
        concept_data = [new_code, concept_pickle['name'].values[0], concept_pickle['c_name'].values[0]]
    else:
        concept_data = [code, concept_pickle['name'].values[0], concept_pickle['c_name'].values[0]]
    insert_concept_table(table_name,concept_data)
print("concept is OK")

for i in range(len(area_tf)):
    area_pickle = area_tf[area_tf.index == i]
    code = str(area_pickle['code'].values[0])
    if len(code) == 3:
        new_code = '000' + code
        area_data = [new_code, area_pickle['name'].values[0], area_pickle['area'].values[0]]
    elif len(code) == 4:
        new_code = '00' + code
        area_data = [new_code, area_pickle['name'].values[0], area_pickle['area'].values[0]]
    elif len(code) == 5:
        new_code = '0' + code
        area_data = [new_code, area_pickle['name'].values[0], area_pickle['area'].values[0]]
    elif len(code) == 2:
        new_code = '0000' + code
        area_data = [new_code, area_pickle['name'].values[0], area_pickle['area'].values[0]]
    elif len(code) == 1:
        new_code = '00000' + code
        area_data = [new_code, area_pickle['name'].values[0], area_pickle['area'].values[0]]
    else:
        area_data = [code, area_pickle['name'].values[0], area_pickle['area'].values[0]]
    insert_area_table(table_name,area_data)

print("area is OK")
